package cn.edu.seu.herald.informationplatform.dao.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Map;

import com.mysql.jdbc.Statement;

import cn.edu.seu.herald.informationplatform.bean.league.Activity;

public class ActivityDao extends DataAccessObject{
	private static ActivityDao daoInstance=null;	
	//Constructor of LeagueDao
	protected ActivityDao(){	
	}
	//Get LeagueDao instance
	public static ActivityDao getDao(){
		if(daoInstance==null){
			daoInstance=new ActivityDao();
		}
		return daoInstance;
	}
	//operations of lg_activity
	
	//judge have id
	public boolean hasId(int id) throws SQLException{
		Connection con=getConnectionLeague();
		try {
			PreparedStatement ps = con.prepareStatement("Select * from lg_activity where id=?");
			ps.setInt(1,id);
			ResultSet rs = ps.executeQuery();
			int i = 0;
			while(rs.next()){
			i = rs.getRow();
			}
			closeRs(rs);
			if(i>0){
			return true;
			}else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		finally{
			closeCon(con);
		}
	}
	
	//insert a item of activity 
	public int addActivity(Activity newActivity)throws SQLException{
		Connection con=getConnectionLeague();
		try {
			PreparedStatement ps=con.prepareStatement("insert into lg_activity(class_id,name,start_time,end_time,information,pic_wall_address,contact,organizer,league_id,release_time,top_counter,post_address) values(?,?,?,?,?,?,?,?,?,?,?,?);",Statement.RETURN_GENERATED_KEYS);
			ps.setInt(1,newActivity.getClassId());
			ps.setString(2,newActivity.getName());
			ps.setString(3,newActivity.getStartTime());
			ps.setString(4,newActivity.getEndTime());
			ps.setString(5,newActivity.getInformation());
			ps.setString(6,newActivity.getPicWallAddress());
			ps.setString(7,newActivity.getContact());
			ps.setString(8,newActivity.getOrganizer());
			ps.setInt(9,newActivity.getLeagueId());
			ps.setDate(10,newActivity.getReleaseTime());
			ps.setInt(11,newActivity.getApplicantCount());
			ps.setString(12,newActivity.getPostAddress());
			ps.executeUpdate();
			ResultSet rsResultSet=ps.getGeneratedKeys();
			int id=-1;
			while (rsResultSet.next()){
				id=rsResultSet.getInt(1);
			}
			closeRs(rsResultSet);
			return id;
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		finally{
			closeCon(con);
		}
	}
	
	
	//delete a activity item
	public void deleteActivity(int id)throws SQLException{
		Connection con=getConnectionLeague();
		try {
			PreparedStatement ps=con.prepareStatement("delete from lg_activity where id=?");
			ps.setInt(1,id);	
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		finally{
			closeCon(con);
		}
	}
	
	//edit a activity
	public Activity updateActivity(int id,Activity newActivity)throws SQLException{

		Connection con=getConnectionLeague();
		try {
			PreparedStatement ps=con.prepareStatement("update lg_activity set class_id=?,name=?,start_time=?,end_time=?,information=?,pic_wall_address=?,contact=?,organizer=?,league_id=?,release_time=?,top_counter=?,post_address=? where id=?");
			ps.setInt(1,newActivity.getClassId());
			ps.setString(2,newActivity.getName());
			ps.setString(3,newActivity.getStartTime());
			ps.setString(4,newActivity.getEndTime());
			ps.setString(5,newActivity.getInformation());
			ps.setString(6,newActivity.getPicWallAddress());
			ps.setString(7,newActivity.getContact());
			ps.setString(8,newActivity.getOrganizer());
			ps.setInt(9,newActivity.getLeagueId());
			ps.setDate(10,newActivity.getReleaseTime());
			ps.setInt(11,newActivity.getApplicantCount());
			ps.setString(12,newActivity.getPostAddress());
			ps.setInt(13,id);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		finally{
			closeCon(con);
		}
		return newActivity;
	}
	//get new activities
	public Map<Integer, Activity> getNewActivitiesByLimitAndLeagueId(int id, int offset, int row) throws SQLException {
    	Connection con=getConnectionLeague();
    	Map<Integer,Activity> idActivityMap=new LinkedHashMap<Integer, Activity>();
    	try{
    	PreparedStatement ps=con.prepareStatement("SELECT * FROM lg_activity WHERE league_id=? ORDER BY release_time DESC LIMIT ?, ?;");
    	ps.setInt(1,id);
    	ps.setInt(2,offset);
    	ps.setInt(3,row);
    	ResultSet rsResultSet=ps.executeQuery();
    	while(rsResultSet.next()){
    		Activity activity=new Activity();
    		activity.setClassId(rsResultSet.getInt(2));
    		activity.setName(rsResultSet.getString(3));
    		activity.setStartTime(rsResultSet.getString(4));
    		activity.setEndTime(rsResultSet.getString(5));
    		activity.setInformation(rsResultSet.getString(6));
    		activity.setPostAddress(rsResultSet.getString(7));
    		activity.setContact(rsResultSet.getString(8));
    		activity.setOrganizer(rsResultSet.getString(9));
    		activity.setApplicantCount(rsResultSet.getInt(10));
    		activity.setReleaseTime(rsResultSet.getDate(12));
    		activity.setPicWallAddress(rsResultSet.getString(13));
    		activity.setApplicantCount(rsResultSet.getInt(14));
    		idActivityMap.put(rsResultSet.getInt(1),activity);
    	}
    	return idActivityMap;
    	}catch (SQLException e){
		e.printStackTrace();
		throw e;
	}
	finally{
		closeCon(con);
	}
	}
	//get hot activities
	public Map<Integer, Activity> getHotActivitiesByLimitAndLeagueId(int id, int offset, int row) throws SQLException {
    	Connection con=getConnectionLeague();
    	Map<Integer,Activity> idActivityMap=new LinkedHashMap<Integer, Activity>();
    	try{
    	PreparedStatement ps=con.prepareStatement("SELECT * FROM lg_activity WHERE league_id=? ORDER BY top_counter DESC LIMIT ?, ?;");
    	ps.setInt(1,id);
    	ps.setInt(2,offset);
    	ps.setInt(3,row);
    	ResultSet rsResultSet=ps.executeQuery();
    	while(rsResultSet.next()){
    		Activity activity=new Activity();
    		activity.setClassId(rsResultSet.getInt(2));
    		activity.setName(rsResultSet.getString(3));
    		activity.setStartTime(rsResultSet.getString(4));
    		activity.setEndTime(rsResultSet.getString(5));
    		activity.setInformation(rsResultSet.getString(6));
    		activity.setPostAddress(rsResultSet.getString(7));
    		activity.setContact(rsResultSet.getString(8));
    		activity.setOrganizer(rsResultSet.getString(9));
    		activity.setApplicantCount(rsResultSet.getInt(10));
    		activity.setReleaseTime(rsResultSet.getDate(12));
    		activity.setPicWallAddress(rsResultSet.getString(13));
    		activity.setApplicantCount(rsResultSet.getInt(14));
    		idActivityMap.put(rsResultSet.getInt(1),activity);
    	}
    	return idActivityMap;
    	}catch (SQLException e){
		e.printStackTrace();
		throw e;
	}
	finally{
		closeCon(con);
	}
	}
	
	public int getNumOfActivities() throws SQLException {
    	Connection con=getConnectionLeague();
    	int num = -1;
    	try{
    	PreparedStatement ps=con.prepareStatement("SELECT COUNT(*) FROM lg_activity;");
    	ResultSet rsResultSet=ps.executeQuery();
    	while(rsResultSet.next()){
    		num = rsResultSet.getInt(1);
    	}
    	return num;
    	}catch (SQLException e){
		e.printStackTrace();
		throw e;
	}
	finally{
		closeCon(con);
	}
	}
//get new activities
	public int getNumOfActivitiesByLeagueId(int id) throws SQLException {
    	Connection con=getConnectionLeague();
    	int num = -1;
    	try{
    	PreparedStatement ps=con.prepareStatement("SELECT COUNT(*) FROM lg_activity WHERE league_id = ?;");
    	ps.setInt(1, id);
    	ResultSet rsResultSet=ps.executeQuery();
    	while(rsResultSet.next()){
    		num = rsResultSet.getInt(1);
    	}
    	return num;
    	}catch (SQLException e){
		e.printStackTrace();
		throw e;
	}
	finally{
		closeCon(con);
	}
	}
	
	public Map<Integer, Activity> getNewActivitiesByLimit(int offset, int row) throws SQLException {
    	Connection con=getConnectionLeague();
    	Map<Integer,Activity> idActivityMap=new LinkedHashMap<Integer, Activity>();
    	try{
    	PreparedStatement ps=con.prepareStatement("SELECT * FROM lg_activity ORDER BY release_time DESC LIMIT ?, ?;");
    	ps.setInt(1,offset);
    	ps.setInt(2,row);
    	ResultSet rsResultSet=ps.executeQuery();
    	while(rsResultSet.next()){
    		Activity activity=new Activity();
    		activity.setClassId(rsResultSet.getInt(2));
    		activity.setName(rsResultSet.getString(3));
    		activity.setStartTime(rsResultSet.getString(4));
    		activity.setEndTime(rsResultSet.getString(5));
    		activity.setInformation(rsResultSet.getString(6));
    		activity.setPostAddress(rsResultSet.getString(7));
    		activity.setContact(rsResultSet.getString(8));
    		activity.setOrganizer(rsResultSet.getString(9));
    		activity.setApplicantCount(rsResultSet.getInt(10));
    		activity.setReleaseTime(rsResultSet.getDate(12));
    		activity.setPicWallAddress(rsResultSet.getString(13));
    		activity.setApplicantCount(rsResultSet.getInt(14));
    		idActivityMap.put(rsResultSet.getInt(1),activity);
    	}
    	return idActivityMap;
    	}catch (SQLException e){
		e.printStackTrace();
		throw e;
	}
	finally{
		closeCon(con);
	}
	}
	
//get activities
    public Map<Integer,Activity> getActivitiesByLeagueId(int leagueId) throws SQLException{
    	Connection con=getConnectionLeague();
    	HashMap<Integer,Activity> idActivityMap=new HashMap<Integer, Activity>();
    	try{
    	PreparedStatement ps=con.prepareStatement("select * from lg_activity where league_id=?");
    	ps.setInt(1,leagueId);
    	ResultSet rsResultSet=ps.executeQuery();
    	while(rsResultSet.next()){
    		Activity activity=new Activity();
    		activity.setClassId(rsResultSet.getInt(2));
    		activity.setName(rsResultSet.getString(3));
    		activity.setStartTime(rsResultSet.getString(4));
    		activity.setEndTime(rsResultSet.getString(5));
    		activity.setInformation(rsResultSet.getString(6));
    		activity.setPostAddress(rsResultSet.getString(7));
    		activity.setContact(rsResultSet.getString(8));
    		activity.setOrganizer(rsResultSet.getString(9));
    		activity.setApplicantCount(rsResultSet.getInt(10));
    		activity.setReleaseTime(rsResultSet.getDate(12));
    		activity.setPicWallAddress(rsResultSet.getString(13));
    		activity.setApplicantCount(rsResultSet.getInt(14));
    		idActivityMap.put(rsResultSet.getInt(1),activity);
    	}
    	return idActivityMap;
    	}catch (SQLException e){
		e.printStackTrace();
		throw e;
	}
	finally{
		closeCon(con);
	}
	}
    
	//get a object of Activity by id 
	public Activity getActivityById(int id) throws SQLException{
		Connection con=getConnectionLeague();
		try {
			PreparedStatement ps=con.prepareStatement("select class_id,name,start_time,end_time,information,post_address,contact,organizer,league_id,release_time,top_counter,pic_wall_address from lg_activity where id=?;");
			Activity newActivity=new Activity();
			ps.setInt(1,id);
			ResultSet rs=ps.executeQuery();
		    while(rs.next()){
		    	newActivity.setClassId(rs.getInt(1));
				newActivity.setName(rs.getString(2));
				newActivity.setStartTime(rs.getString(3));
				newActivity.setEndTime(rs.getString(4));
				newActivity.setInformation(rs.getString(5));
				newActivity.setPostAddress(rs.getString(6));
				newActivity.setContact(rs.getString(7));
				newActivity.setOrganizer(rs.getString(8));
				newActivity.setLeagueId(rs.getInt(9));
				newActivity.setReleaseTime(rs.getDate(10));
				newActivity.setApplicantCount(rs.getInt(11));
				newActivity.setPicWallAddress(rs.getString(12));
		    }
			closeRs(rs);
			return newActivity;
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		finally{
			closeCon(con);	
		}
	}
	
	//addCounter
	public void addCounterByActivityId(int id) throws SQLException{
		Connection con=getConnectionLeague();
		try {
			PreparedStatement ps=con.prepareStatement("UPDATE lg_activity SET top_counter=top_counter+1 WHERE id=?;");
			ps.setInt(1,id);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		finally{
			closeCon(con);	
		}
	}

}
